﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LinqToExcel;
using System.Linq.Expressions;
using System.Reflection;
using System.IO;


public class ExcelHelper
{
    private ExcelQueryFactory excel;


    public ExcelHelper()
    {
        //excel = new ExcelQueryFactory(filePath);
    }

    public ExcelHelper(string filePath)
    {
        excel = new ExcelQueryFactory(filePath);
    }

    public List<string> GetWorksheetNames()
    {
        return excel.GetWorksheetNames().ToList();
    }

    public void AddMapping<TModel>(Expression<Func<TModel, object>> property, string column)
    {
        if (excel != null)
        {
            excel.AddMapping<TModel>(property, column);
        }
    }

    /// <summary>
    /// sheet1
    /// </summary>
    /// <typeparam name="TModel"></typeparam>
    /// <returns></returns>
    public IList<TModel> Query<TModel>()
    {
        if (excel != null)
        {
            return excel.Worksheet<TModel>().ToList();
        }

        return null;
    }

    /// <summary>
    /// 指定sheetname
    /// </summary>
    /// <typeparam name="TModel"></typeparam>
    /// <param name="sheetName"></param>
    /// <returns></returns>
    public IList<TModel> Query<TModel>(string sheetName)
    {
        if (excel != null)
        {
            return excel.Worksheet<TModel>(sheetName).ToList();
        }

        return null;
    }

    /// <summary>
    /// 指定第sheetIndex个sheet
    /// </summary>
    /// <typeparam name="TModel"></typeparam>
    /// <param name="sheetName"></param>
    /// <returns></returns>
    public IList<TModel> Query<TModel>(int sheetIndex)
    {
        if (excel != null)
        {
            return excel.Worksheet<TModel>(sheetIndex).ToList();
        }

        return null;
    }

    /// <summary>
    /// 需要确定导出的model
    /// </summary>
    /// <typeparam name="TModel"></typeparam>
    /// <param name="list"></param>
    public void Write(Stream s, IQueryable list)
    {
        using (System.IO.Stream stream = s)
        {
            using (System.IO.StreamWriter writer = new System.IO.StreamWriter(stream, Encoding.Unicode, 30))
            {
                string data = FormatExcelData(list);//调用函数
                if (data == string.Empty) return;
                byte[] tmp;
                tmp = Encoding.Unicode.GetBytes(data);
                writer.Write(data);
                writer.Close();
            }
            stream.Close();
        }
    }

    private string FormatExcelData(IQueryable list)
    {

        System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
        List<string> keys = new List<string>();
        foreach (object result in list)
        {
            PropertyInfo[] properties = result.GetType().GetProperties();
            keys = (from property in properties
                    select property.Name).ToList();
            break;
        }

        //header
        List<string> headers = new List<string>();
        foreach (string header in keys)
        {
            headers.Add(FormatCSVField(header, (keys.IndexOf(header) == 0)));
        }
        strBuilder.Append(String.Join("", headers.ToArray())).Append("\t\n");

        //data
        foreach (object result in list)
        {
            List<string> data = new List<string>();
            foreach (string key in keys)
            {
                string nvalue = "";
                try
                {
                    nvalue = result.GetType().GetProperty(key).GetValue(result, null).ToString();
                }
                catch
                {
                    nvalue = "";
                }

                data.Add(FormatCSVField(nvalue, (keys.IndexOf(key) == 0)));
            }
            strBuilder.Append(String.Join("", data.ToArray())).Append("\t\n");
        }

        return strBuilder.ToString();

    }

    private string FormatCSVField(string data, bool first)
    {
        if (first)
            return data.Replace("\"", "\t\n");
        else
            return String.Format("\t{0}", data.Replace("\"", "\t\n"));
    }
}
 
